#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @time     : 2019-05-23 18:15
# @Author   : BYF
# file      : excel.py
# @version  :

import os
import xlrd
import json
import pandas as pd


class Excel(object):
    """
    Excel
    """

    def __init__(self):
        """
        初始化
        """
        pass

    def read_excel(self, path):
        """
        读取Excel
        :param path: 用例路径
        :return: data
            interface_name -> str 接口名称
            case_name -> list 用例名称；eq: ["用例1","用例2","用例3"]
            case_data -> list 用例参数
                url -> 接口请求地址
                headers -> 接口请求headers
                payload -> 接口请求入参
                expected -> 用例预期结果
                response -> 接受接口返回response，会写用例坐标
                result -> 接受用例执行结果，会写用例坐标
        """

        try:
            df = pd.read_excel(path)
        except Exception as e:
            print("读取用例失败，请检查~！当前用例路径为：{path}".format(path=path), e)
            return False
        # rows_num = len((list(df.values)))  # 获取表格总行数
        rows_num = pd.DataFrame(df).shape[0]  # 获取表格总行数

        # cols_num = len(df.columns)  # 获取表格列行数
        cols_num = pd.DataFrame(df).shape[1]  # 获取表格总列数

        url = df.loc[3][1]  # 获取接口地址 指定行 和 指定列的数据（去掉了表头）
        if not url.startswith("/"):
            url = "/" + url

        interface_name = df.loc[5][1]  # 获取接口名字（第7行 第2列的数据）
        headers = eval(df.loc[7][1])  # 获取headers

        content_type = headers.get("Content-Type")
        if content_type is None:
            payload_type = "dict"
        elif content_type == "application/x-www-form-urlencoded":
            payload_type = "dict"
        elif content_type == "application/json;charset=UTF-8":
            payload_type = "json"

        case_name = []
        case_data = []

        for i in range(10, rows_num):
            name = df.loc[i][1]  # 用例名称
            case_name.append(name)

            str_type = df.loc[i][3]  # 请求参数类型
            str = df.loc[i][2]  # 请求参数
            payload = self.str_to_dict(str, str_type)

            # expected = df.loc[i][4]  # 预期结果
            expected = eval(df.loc[i][5])  # 预期结果
            case_data.append([name, url, headers, payload, payload_type, expected])
        return interface_name, case_name, case_data

    @staticmethod
    def write_excel(path, data):
        """
        写入Excel
        :param path:
        :param data:
         data = [
            [{"id": "WD", "device": "weather", "timestamp": 1553783825903}],
            [{"id": "WD", "device": "weather", "timestamp": 1553783825904}],
            [{"id": "WD", "device": "weather", "timestamp": 1553783825905}],
        ]
        :return:
        """
        df = pd.DataFrame()
        for line in data:
            for i in line:
                df1 = pd.DataFrame([i])
                df = df.append(df1)
        df.to_excel(path, startrow=0, index=False)
        return "保存成功"

    @staticmethod
    def str_to_dict(string, type):
        """
        将用例中的入参转化为dict类型
        :param str:
        :param type:
        :return:
        """

        if type == "json":
            data = eval(string)
            return data
        else:
            data = dict()
            try:
                temp_list = string.split("\n")  # 通过换行符 \n 切割
                for temp in temp_list:  # 循环list中的每一个元素，通过第一个 : 切割
                    temp_str = temp.split(":", 1)
                    data[temp_str[0]] = temp_str[1].replace(" ", "")
                return data
            except Exception as e:
                print(e)
                print(f"参数格式错误，请检查！当前参数为：{string}")

    def is_json(self, str):
        """
        判断str是否是标准的json格式
        :param str:
        :return:
        """
        try:
            json_object = json.loads(str)
        except ValueError as e:
            print(e)
            return False
        return True


def read_excel(file_path, sheet_name=None):
    """
    读取Excel
    :param file_path:
    :param sheet_name:
    :return:
    """
    case_name = []
    case_data = []
    try:
        excel = xlrd.open_workbook(file_path)
        if sheet_name in excel.sheet_names():
            table = excel.sheet_by_name(sheet_name)
        else:
            table = excel.sheet_by_index(0)
        list = []
        for i in range(1, table.nrows):
            title = table.row_values(0)
            row_content = table.row_values(i, 0, table.ncols)
            list.append(dict(zip(title, row_content)))
        return list
    except Exception as e:
        print(e)


if __name__ == '__main__':
    project_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    path = os.path.join(project_path, "params/api/marketing_coupon/test_add_coupon_template.xlsx")
    # path = os.path.join(project_path, "params/api/marketing_coupon/test_app_find_can_use_coupon_list.xlsx")

    # interface_name, case_name, case_data = Excel().read_excel(path)
    # for i in case_data:
    #     print(i)
    # str = case_data[1][3]

    # print(case_data)
    # print(type(case_data))
    # print("*" * 100)
    # print(case_data[0])
    # print(type(case_data[0][3]))

    # create some Pandas DateFrame from some data
    # df1 = pd.DataFrame({'Data1': [1, 2, 3, 4, 5, 6, 7]})
    # df2 = pd.DataFrame({'Data2': [8, 9, 10, 11, 12, 13]})
    # df3 = pd.DataFrame({'Data3': [14, 15, 16, 17, 18]})
    # All = [df1, df2, df3]
    # create a Pandas Excel writer using xlswriter
    # writer = pd.ExcelWriter(path)

    # df1.to_excel(writer, sheet_name='Data1', startcol=0, index=False)
    # df2.to_excel(writer, sheet_name='Data1', startcol=1, index=False)
    # df3.to_excel(writer, sheet_name='Data3', index=False)
    # writer.save()
    # print(Excel.read_excel(path))
